use [efy-doc-hquangninh]
-------------------------------------------------------List Procedure Process Document Receive-------------------------------------------------------------------
/*****************************************************************************************************************************************************
*	Ngay tao:	20/07/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	cap nhat chuyen muc
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Web_MenuUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Web_MenuUpdate]
GO
CREATE PROCEDURE [dbo].[Web_MenuUpdate]
	@p_menu_id					Varchar(50)	
	,@p_parent_menu_id			Varchar(50)	
	,@p_name					Nvarchar(200)
	,@p_lever					TinyInt
	,@p_url						Nvarchar(200)
	,@p_article_id				varchar(50)
	,@p_position				TinyInt
	,@p_web_display_status		TinyInt
	,@p_windows_open			TinyInt
	,@p_order					Int	
	,@p_status					TinyInt
	,@p_public_view				TinyInt
	,@p_edit_staff_id_list		Nvarchar(4000)
	,@p_approve_staff_id_list	Nvarchar(4000)
	,@p_view_staff_id_list		Nvarchar(4000)
	,@p_owner_code_list			Nvarchar(4000)
WITH ENCRYPTION
AS
	Declare @sNewWebMenuId	Varchar(50)
			,@v_filter nvarchar(4000)
			,@iOrder1 int
			,@iOrder2 int
			,@iOrder3 int
			,@icount Int				-- Bien luu so phan tu cua list	
			,@iIndex Int				-- Bien duyet phan tu tren list
			,@sIndexId Varchar(50)		-- Luu Id cua chi so 
			,@icountId	Int				-- Bien dem Id
			,@sMenuID Varchar(50)	
			,@iPosition TinyInt
			,@sArticleTitle Nvarchar(1000)	
	SET NOCOUNT ON
	BEGIN TRANSACTION
		set @iOrder1 = 0
		set @iOrder2 = 0
		set @iOrder3 = 0
		if (@p_lever = 0)
			begin
				set @iOrder1 = @p_order
				set @iOrder2 = 0
				set @iOrder3 = 0
			end
		if (@p_lever = 1)
			begin
				select @iOrder1 = C_ORDER_LEVER1 from T_WEB_MENU where PK_WEB_MENU = @p_parent_menu_id
				set @iOrder2 = @p_order
				set @iOrder3 = 0
			end
		if (@p_lever = 2)
			begin
				select @sMenuID = FK_WEB_MENU from T_WEB_MENU where PK_WEB_MENU = @p_parent_menu_id
				select @iOrder1 = C_ORDER_LEVER1 from T_WEB_MENU where PK_WEB_MENU = @sMenuID
				select @iOrder2 = C_ORDER_LEVER1 from T_WEB_MENU where PK_WEB_MENU = @p_parent_menu_id
				set @iOrder3 = @p_order
			end
		-- Lay vi tri hien thi
		if @p_parent_menu_id is not null and @p_parent_menu_id <> ''
				select @iPosition = C_POSITION from T_WEB_MENU where PK_WEB_MENU = @p_parent_menu_id
		else
				set @iPosition = @p_position
		-- Hieu chinh chuyen muc
		If @p_menu_id is not null And @p_menu_id <> ''
			Begin
				Update T_WEB_MENU 
				Set FK_WEB_MENU			= NULL
					,C_NAME				= @p_name
					,C_LEVEL			= @p_lever
					,C_URL				= @p_url
					,C_POSITION			= @iPosition 
					,C_WEB_DISPLAY		= @p_web_display_status
					,C_WINDOWS_OPEN		= @p_windows_open
					,C_ORDER_LEVER1		= @iOrder1
					,C_ORDER_LEVER2		= @iOrder2
					,C_ORDER_LEVER3		= @iOrder3
					,C_STATUS			= @p_status
					,C_PUBLIC_VIEW		= @p_public_view
					,C_OWNER_CODE_LIST	= @p_owner_code_list 
				Where PK_WEB_MENU = @p_menu_id
				Set @sNewWebMenuId = @p_menu_id
			end
		-- Them moi chuyen muc
		else 
			begin
				set @sNewWebMenuId = newid()
				Insert into T_WEB_MENU(
					PK_WEB_MENU
					,C_NAME	
					,C_LEVEL
					,C_URL	
					,C_POSITION	
					,C_WEB_DISPLAY
					,C_WINDOWS_OPEN
					,C_ORDER_LEVER1
					,C_ORDER_LEVER2
					,C_ORDER_LEVER3
					,C_STATUS	
					,C_PUBLIC_VIEW
					,C_OWNER_CODE_LIST
				) 
				values( 
					@sNewWebMenuId
					,@p_name
					,@p_lever
					,@p_url
					,@iPosition 
					,@p_web_display_status
					,@p_windows_open
					,@iOrder1
					,@iOrder2
					,@iOrder3
					,@p_status
					,@p_public_view
					,@p_owner_code_list ) 
			end
		-- Update lai tin bai lien quan
		if @p_article_id is not null and @p_article_id <> ''
			begin
				Update T_WEB_MENU set FK_WEB_ARTICLE = @p_article_id where PK_WEB_MENU = @sNewWebMenuId
				select @sArticleTitle = C_TITLE from T_WEB_ARTICLE where PK_WEB_ARTICLE = @p_article_id
				Update T_WEB_MENU set C_ARTICLE_TITLE = @sArticleTitle where PK_WEB_MENU = @sNewWebMenuId
			end
		else
			begin
				Update T_WEB_MENU set FK_WEB_ARTICLE = NULL,C_ARTICLE_TITLE = NULL where PK_WEB_MENU = @sNewWebMenuId
			end
		-- Update lai CHUYEN MUC GOC
		if @p_parent_menu_id is not null and @p_parent_menu_id <> ''
			Update T_WEB_MENU set FK_WEB_MENU = @p_parent_menu_id where PK_WEB_MENU = @sNewWebMenuId
		-- Sap xep lai thu tu hien thi chuyen muc cap 0
		if (@p_lever = 0 and @p_order > 0)
			begin
				Set @v_filter = '(C_LEVEL = 0 and C_POSITION =' + char(39) + convert(varchar,@iPosition) + char(39)+ ')'
				Exec SP_ReOrder 'T_WEB_MENU', 'C_ORDER_LEVER1', 'PK_WEB_MENU', @sNewWebMenuId, @iOrder1,@v_filter	
				Update T_WEB_MENU set C_ORDER_LEVER1 = dbo.f_getorder(FK_WEB_MENU) where C_LEVEL = 1 and C_POSITION = @iPosition
				Update T_WEB_MENU set C_ORDER_LEVER1 = dbo.f_getorder(dbo.f_getParebID(FK_WEB_MENU)), C_ORDER_LEVER2 = dbo.f_getorder1(FK_WEB_MENU) where C_LEVEL = 2 and C_POSITION = @iPosition
			end

		if (@p_lever = 1 and @p_order > 0)
			begin
				Set @v_filter = '(C_LEVEL = 1 and C_POSITION =' + char(39) + convert(varchar,@iPosition) + char(39)+ 'and FK_WEB_MENU =' + char(39) + @p_parent_menu_id + char(39)+ ')'
				Exec SP_ReOrder 'T_WEB_MENU', 'C_ORDER_LEVER2', 'PK_WEB_MENU', @sNewWebMenuId, @iOrder2,@v_filter	
				Update T_WEB_MENU set C_ORDER_LEVER2 = dbo.f_getorder1(FK_WEB_MENU) where C_LEVEL = 2 and C_POSITION = @iPosition
			end
		-- Sap xep lai thu tu hien thi chuyen muc cap 2
		if (@p_lever = 2 and @p_order > 0)
			begin
				Set @v_filter = '(C_LEVEL = 2 and FK_WEB_MENU =' + char(39) + @p_parent_menu_id + char(39)+ ')'
				Exec SP_ReOrder 'T_WEB_MENU', 'C_ORDER_LEVER3', 'PK_WEB_MENU', @sNewWebMenuId, @iOrder3,@v_filter	
				Update T_WEB_MENU set C_ORDER_LEVER2 = dbo.f_getorder1(FK_WEB_MENU) where C_LEVEL = 2 and C_POSITION = @iPosition
			end
		-- Update lai vi tri cac chuyen muc con
		if (@p_lever = 0)
			begin
				Update T_WEB_MENU set C_POSITION = @iPosition where C_LEVEL = 1 and FK_WEB_MENU = @sNewWebMenuId			
				Update T_WEB_MENU set C_POSITION = @iPosition where C_LEVEL = 2 and dbo.f_getParebID(FK_WEB_MENU) = @sNewWebMenuId			
			end
		-- Cap nhat can bo co quyen sua tin bai
		update T_WEB_MENU_ROLE set C_EDIT = 0 where FK_WEB_MENU = @sNewWebMenuId
		if @p_edit_staff_id_list is not null and @p_edit_staff_id_list <> ''
			Begin
				Set @icount = 0
				Set @iIndex = 0
				Set @sIndexId	= ''
				Set @icount = dbo.f_ListLen(@p_edit_staff_id_list,',')
				Set @iIndex = 1
					While @iIndex <= @icount
						Begin
							Set @sIndexId = dbo.f_ListGetAt(@p_edit_staff_id_list,@iIndex,',')	
							select @icountId = count(PK_WEB_MENU_ROLE) from T_WEB_MENU_ROLE where FK_STAFF = @sIndexId and FK_WEB_MENU = @sNewWebMenuId	
							if @icountId > 0
								begin
									update T_WEB_MENU_ROLE set C_EDIT = 1 where FK_STAFF = @sIndexId and FK_WEB_MENU = @sNewWebMenuId
								end
							else
								begin
									Insert Into T_WEB_MENU_ROLE(
											PK_WEB_MENU_ROLE
											,FK_WEB_MENU
											,FK_STAFF
											,C_EDIT
											,C_APPROVE
											,C_VIEW
											)
									Values(	
											newid()
											,@sNewWebMenuId
											,@sIndexId
											,1
											,0
											,1
											)
								end
							Set @iIndex = @iIndex + 1	--Lay LANH DAO tiep theo							
						End
			End
		-- Cap nhat can bo co quyen duyet tin bai
		update T_WEB_MENU_ROLE set C_APPROVE = 0 where FK_WEB_MENU = @sNewWebMenuId
		if @p_approve_staff_id_list is not null and @p_approve_staff_id_list <> ''
			Begin
				Set @icount = 0
				Set @iIndex = 0
				Set @sIndexId	= ''
				Set @icount = dbo.f_ListLen(@p_approve_staff_id_list,',')
				Set @iIndex = 1
					While @iIndex <= @icount
						Begin
							Set @sIndexId = dbo.f_ListGetAt(@p_approve_staff_id_list,@iIndex,',')	
							select @icountId = count(PK_WEB_MENU_ROLE) from T_WEB_MENU_ROLE where FK_STAFF = @sIndexId and FK_WEB_MENU = @sNewWebMenuId	
							if @icountId > 0
								begin
									update T_WEB_MENU_ROLE set C_APPROVE = 1 where FK_STAFF = @sIndexId and FK_WEB_MENU = @sNewWebMenuId
								end
							else
								begin
									Insert Into T_WEB_MENU_ROLE(
											PK_WEB_MENU_ROLE
											,FK_WEB_MENU
											,FK_STAFF
											,C_EDIT
											,C_APPROVE
											,C_VIEW
											)
									Values(	
											newid()
											,@sNewWebMenuId
											,@sIndexId
											,0
											,1
											,1
											)
								end
							Set @iIndex = @iIndex + 1	--Lay LANH DAO tiep theo							
						End
			End
		-- Cap nhat can bo co quyen xem tin bai
		update T_WEB_MENU_ROLE set C_VIEW = 0 where FK_WEB_MENU = @sNewWebMenuId
		if @p_view_staff_id_list is not null and @p_view_staff_id_list <> '' and @p_public_view = 0
			Begin
				Set @icount = 0
				Set @iIndex = 0
				Set @sIndexId	= ''
				Set @icount = dbo.f_ListLen(@p_view_staff_id_list,',')
				Set @iIndex = 1
					While @iIndex <= @icount
						Begin
							Set @sIndexId = dbo.f_ListGetAt(@p_view_staff_id_list,@iIndex,',')	
							select @icountId = count(PK_WEB_MENU_ROLE) from T_WEB_MENU_ROLE where FK_STAFF = @sIndexId and FK_WEB_MENU = @sNewWebMenuId	
							if @icountId > 0
								begin
									update T_WEB_MENU_ROLE set C_VIEW = 1 where FK_STAFF = @sIndexId and FK_WEB_MENU = @sNewWebMenuId
								end
							else
								begin
									Insert Into T_WEB_MENU_ROLE(
											PK_WEB_MENU_ROLE
											,FK_WEB_MENU
											,FK_STAFF
											,C_EDIT
											,C_APPROVE
											,C_VIEW
											)
									Values(	
											newid()
											,@sNewWebMenuId
											,@sIndexId
											,0
											,0
											,1
											)
								end
							Set @iIndex = @iIndex + 1	--Lay LANH DAO tiep theo							
						End
			End
		Select @sNewWebMenuId NEW_ID
	COMMIT TRANSACTION 
	SET NOCOUNT OFF
Return 0
GO
/*
Exec Web_MenuUpdate '','A303BAAD-DCCB-439D-982F-F96B3E8C3A4D','dfdsf','1','','','1','0','0','1','1','1','','','',''
Exec Web_MenuUpdate 'CD09D9D9-6251-4D08-9735-DCCA69F5E7A0','','THONG BAO','0','','','0','0','0','1','1','1','6A0F2F62-BC1E-4D7D-BA01-6660FC760681','6A0F2F62-BC1E-4D7D-BA01-6660FC760681','6A0F2F62-BC1E-4D7D-BA01-6660FC760681','HQN,XLN,XDN,XHAN,XXN'
Exec Web_MenuUpdate '','','THONG BAO','0','','','0','1','0','1','1','1','6A0F2F62-BC1E-4D7D-BA01-6660FC760681','6A0F2F62-BC1E-4D7D-BA01-6660FC760681','','HQN,XLN,XDN,XHAN,XXN'
*/
/*****************************************************************************************************************************************************
*	Ngay tao:	20/07/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	Lay danh sach chuyen muc theo vi tri hien thi
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Web_MenuGetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Web_MenuGetAll]
GO
CREATE PROCEDURE [dbo].[Web_MenuGetAll]
	@p_position TinyInt		-- Vi tri hien thi/ ben trai/ ben phai/...
	,@p_owner nvarchar(50) = ''	-- 
	,@p_lever nvarchar(50) = 3	-- 
	,@p_status TinyInt = 2	-- 
WITH ENCRYPTION
AS
	SET NOCOUNT ON
		Declare @sSql nvarchar(4000)
		Create Table #T_ALL_MENU(PK_MENU uniqueidentifier)
		Set @sSql = ' Insert into #T_ALL_MENU(PK_MENU)'
		Set @sSql = @sSql + ' Select PK_WEB_MENU'
		Set @sSql = @sSql + ' From T_WEB_MENU Where 1=1 '
		If @p_position = '0' or @p_position = '1' or @p_position = '2' or @p_position = '3'
			Begin
				Set @sSql = @sSql + ' And C_POSITION = ' + char(39) + convert(varchar,@p_position) + char(39)	
			End
		if @p_owner is not null and @p_owner <> ''
			Begin
				Set @sSql = @sSql + ' And ' + char(39) + @p_owner + char(39) + 'in (select part from dbo.f_ConvertStringToTable(C_OWNER_CODE_LIST,' + char(39) +',' + char(39) + '))'	
			End
		If @p_lever = '0' or @p_lever = '1' or @p_lever = '2'
			Begin
				Set @sSql = @sSql + ' And C_LEVEL = ' + char(39) + convert(varchar,@p_lever) + char(39)	
			End
		If @p_status = '0' or @p_status = '1'
			Begin
				Set @sSql = @sSql + ' And C_STATUS = ' + char(39) + convert(varchar,@p_status) + char(39)	
			End
		--print(@sSql)
		Exec (@sSql)
		SELECT	
			convert(varchar(50),A.PK_MENU) as PK_WEB_MENU
			,convert(varchar(50),B.FK_WEB_MENU) as FK_WEB_MENU
			,B.C_NAME
			,B.C_LEVEL
			,B.C_URL
			,B.C_ORDER_LEVER1
			,B.C_ORDER_LEVER2
			,B.C_ORDER_LEVER3
			,B.C_STATUS
			,B.C_POSITION
			,B.FK_WEB_ARTICLE
			,B.C_WINDOWS_OPEN
			,B.C_WEB_DISPLAY
			,dbo.f_getparenstatus(B.PK_WEB_MENU) as CHECKPARENT
			,B.C_PUBLIC_VIEW
			,dbo.f_GetWebMenuRole(convert(varchar(50),A.PK_MENU),'C_VIEW') as FK_VIEW_STAFF_ID_LIST
		from #T_ALL_MENU A inner join T_WEB_MENU B on A.PK_MENU = B.PK_WEB_MENU
		order by C_POSITION,C_ORDER_LEVER1,C_ORDER_LEVER2,C_ORDER_LEVER3
	SET NOCOUNT OFF
Return 0
GO
/*
Exec Web_MenuGetAll '1'
Exec Web_MenuGetAll '0'
Exec Web_MenuGetAll '3'
Exec Web_MenuGetAll '0','HQN'
select * from dbo.f_ConvertStringToTable('1,2,4,5',',')
*/
/*****************************************************************************************************************************************************
*	Ngay tao:	20/07/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	Lay thong tin chi tiet mot chuyen muc
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Web_MenuGetSingle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Web_MenuGetSingle]
GO
CREATE PROCEDURE [dbo].[Web_MenuGetSingle]
	@p_menu_id Varchar(50)
WITH ENCRYPTION
AS
	SET NOCOUNT ON
		SELECT	
			convert(varchar(50),PK_WEB_MENU) as PK_WEB_MENU
			,convert(varchar(50),FK_WEB_MENU) as FK_WEB_MENU
			,C_NAME
			,C_LEVEL
			,C_URL
			,convert(varchar(50),FK_WEB_ARTICLE) as FK_WEB_ARTICLE
			,C_ARTICLE_TITLE
			,C_POSITION
			,C_WEB_DISPLAY
			,C_WINDOWS_OPEN
			,C_ORDER_LEVER1
			,C_ORDER_LEVER2
			,C_ORDER_LEVER3
			,C_STATUS
			,C_PUBLIC_VIEW
			,C_OWNER_CODE_LIST
			,dbo.f_getparenstatus(PK_WEB_MENU) as CHECKPARENT
			,dbo.f_GetWebMenuRole(convert(varchar(50),PK_WEB_MENU),'C_EDIT') as FK_EDIT_STAFF_ID_LIST
			,dbo.f_GetWebMenuRole(convert(varchar(50),PK_WEB_MENU),'C_APPROVE') as FK_APPROVE_STAFF_ID_LIST
			,dbo.f_GetWebMenuRole(convert(varchar(50),PK_WEB_MENU),'C_VIEW') as FK_VIEW_STAFF_ID_LIST
		from T_WEB_MENU where PK_WEB_MENU = @p_menu_id
	SET NOCOUNT OFF
Return 0
GO
/*****************************************************************************************************************************************************
*	Ngay tao:	20/07/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetWebMenuRole]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_GetWebMenuRole]
GO
CREATE function  [dbo].[f_GetWebMenuRole](@sWebMenuId varchar(50), @sRole nvarchar(10))
Returns nvarchar(4000)
WITH ENCRYPTION	
As
	Begin
		Declare @HTML nvarchar(4000)	
		Set @HTML = ''
		if @sRole = 'C_EDIT' 
			Begin
				Select @HTML = @HTML + convert(varchar(50),FK_STAFF) + ',' From T_WEB_MENU_ROLE Where FK_WEB_MENU = @sWebMenuId And C_EDIT = 1
			End
		if @sRole = 'C_APPROVE' 
			Begin
				Select @HTML = @HTML + convert(varchar(50),FK_STAFF) + ',' From T_WEB_MENU_ROLE Where FK_WEB_MENU = @sWebMenuId And C_APPROVE = 1
			End
		if @sRole = 'C_VIEW' 
			Begin
				Select @HTML = @HTML + convert(varchar(50),FK_STAFF) + ',' From T_WEB_MENU_ROLE Where FK_WEB_MENU = @sWebMenuId And C_VIEW = 1
			End
		If @HTML <> ''
			Begin
				Set @HTML = substring(@HTML,1,len(@HTML)-1)
			End
		Return @HTML
	End
GO

/*****************************************************************************************************************************************************
*	Ngay tao:	20/07/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Web_MenuDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Web_MenuDelete]
GO
CREATE PROCEDURE [dbo].[Web_MenuDelete]
	@sWebMenuIdList 		Nvarchar(4000)
	--,@p_position TinyInt		-- Vi tri hien thi/ ben trai/ ben phai/...
WITH ENCRYPTION
AS
	Declare @count int, @sql varchar(1000)
	SET NOCOUNT ON
		-- Tao bang trung gian
		Create Table #T_TEMP_WEB_MENU(PK_WEB_MENU Varchar(50))
		Exec Sp_ListToTable @sWebMenuIdList, 'PK_WEB_MENU', '#T_TEMP_WEB_MENU', ','	
		select @count = count(*) from T_WEB_MENU where FK_WEB_MENU In (Select PK_WEB_MENU From #T_TEMP_WEB_MENU)
			if @count > 0
				begin
					Select 'Ban phai xoa chuyen muc cap con!' RET_ERROR
					Return -100
				end
		-- SET XACT_ABORT ON -- Dat che do tu dong Rollback neu co loi xay ra
		BEGIN TRANSACTION	
			--Xoa thong tin PHAN QUYEN
			Delete T_WEB_MENU_ROLE Where FK_WEB_MENU In (Select PK_WEB_MENU From #T_TEMP_WEB_MENU)
			--Xoa cac CHUYEN MUC da chon
			Delete T_WEB_MENU Where PK_WEB_MENU In (Select PK_WEB_MENU From #T_TEMP_WEB_MENU)
		COMMIT TRANSACTION
	SET NOCOUNT OFF
	Return 0
GO
/*
Exec Web_MenuDelete '6053891F-41B0-481C-A92A-31E7700C6377'
*/
/*****************************************************************************************************************************************************
*	Ngay tao:	20/07/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	Sap xep lai chuyen muc
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Web_MenuOrderLevel01]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Web_MenuOrderLevel01]
GO
CREATE PROCEDURE [dbo].[Web_MenuOrderLevel01]
	@p_position TinyInt		-- Vi tri hien thi/ ben trai/ ben phai/...
WITH ENCRYPTION
AS
	Declare @count int, @sSql varchar(1000)
	SET NOCOUNT ON
		Create Table #T_ALL_MENU(P_ID int IDENTITY (1,1),PK_MENU uniqueidentifier)
		Set @sSql = ' Insert into #T_ALL_MENU(PK_MENU)'
		Set @sSql = @sSql + ' Select PK_WEB_MENU'
		Set @sSql = @sSql + ' From T_WEB_MENU Where 1=1 '
		Set @sSql = @sSql + ' And C_POSITION = ' + char(39) + convert(varchar,@p_position) + char(39)	
		Set @sSql = @sSql + ' And ((C_LEVEL = 0) or (C_LEVEL = 1))'
		Set @sSql = @sSql + ' Order by C_ORDER_LEVER1'
		Exec (@sSql)
		Update T_WEB_MENU set T_WEB_MENU.C_ORDER_LEVER1 = (select #T_ALL_MENU.P_ID from #T_ALL_MENU where #T_ALL_MENU.PK_MENU = T_WEB_MENU.PK_WEB_MENU) where T_WEB_MENU.PK_WEB_MENU in (select #T_ALL_MENU.PK_MENU from #T_ALL_MENU)
		Update T_WEB_MENU set T_WEB_MENU.C_ORDER_LEVER1 = (select #T_ALL_MENU.P_ID from #T_ALL_MENU where #T_ALL_MENU.PK_MENU = T_WEB_MENU.FK_WEB_MENU) where T_WEB_MENU.C_LEVEL = 2 and T_WEB_MENU.FK_WEB_MENU in (select #T_ALL_MENU.PK_MENU from #T_ALL_MENU)
	SET NOCOUNT OFF
	Return 0
GO
/*
Exec Web_MenuOrderLevel01 '0'
*/
/*****************************************************************************************************************************************************
*	Ngay tao:	20/07/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	Sap xep lai chuyen muc
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Web_MenuOrderLevel2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Web_MenuOrderLevel2]
GO
CREATE PROCEDURE [dbo].[Web_MenuOrderLevel2]
	@p_menu_id Varchar(50)
	,@p_position TinyInt		-- Vi tri hien thi/ ben trai/ ben phai/...
WITH ENCRYPTION
AS
	Declare @count int, @sSql varchar(1000)
	SET NOCOUNT ON
		Create Table #T_ALL_MENU(P_ID int IDENTITY (1,1),PK_MENU uniqueidentifier)
		Set @sSql = ' Insert into #T_ALL_MENU(PK_MENU)'
		Set @sSql = @sSql + ' Select PK_WEB_MENU'
		Set @sSql = @sSql + ' From T_WEB_MENU Where 1=1 '
		Set @sSql = @sSql + ' And FK_WEB_MENU = '+ char(39) + @p_menu_id + char(39)
		Set @sSql = @sSql + ' And C_POSITION = ' + char(39) + convert(varchar,@p_position) + char(39)	
		Set @sSql = @sSql + ' And C_LEVEL = 2'
		Set @sSql = @sSql + ' Order by C_ORDER_LEVER2'
		Exec (@sSql)
		Update T_WEB_MENU set T_WEB_MENU.C_ORDER_LEVER2 = (select #T_ALL_MENU.P_ID from #T_ALL_MENU where #T_ALL_MENU.PK_MENU = T_WEB_MENU.PK_WEB_MENU) where T_WEB_MENU.PK_WEB_MENU in (select #T_ALL_MENU.PK_MENU from #T_ALL_MENU)
		select * from #T_ALL_MENU
	SET NOCOUNT OFF
	Return 0
GO
/*
Exec Web_MenuOrder '0'
*/
/*****************************************************************************************************************************************************
*	Ngay tao:	20/07/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getorder]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_getorder]
GO
CREATE function dbo.f_getorder(@sMenuId varchar(50))
Returns int
WITH ENCRYPTION
As
Begin
	Declare @index int
	select @index = C_ORDER_LEVER1 from T_WEB_MENU where PK_WEB_MENU = @sMenuId
Return @index
End
GO
/*****************************************************************************************************************************************************
*	Ngay tao:	20/07/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getorder1]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_getorder1]
GO
CREATE function dbo.f_getorder1(@sMenuId varchar(50))
Returns int
WITH ENCRYPTION
As
Begin
	Declare @index int
	select @index = C_ORDER_LEVER2 from T_WEB_MENU where PK_WEB_MENU = @sMenuId
Return @index
End
GO
/*****************************************************************************************************************************************************
*	Ngay tao:	20/07/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getParebID]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_getParebID]
GO
CREATE function dbo.f_getParebID(@sMenuId varchar(50))
Returns varchar(50)
WITH ENCRYPTION
As
Begin
	Declare @index varchar(50)
	select @index = FK_WEB_MENU from T_WEB_MENU where PK_WEB_MENU = @sMenuId
Return @index
End
GO
/*
select dbo.f_getParebID('27AA1034-16F0-4E44-A765-0E8066AC2053')
0B614D59-BFB1-4BE3-99E8-97621063688E
select dbo.f_getorder('0B614D59-BFB1-4BE3-99E8-97621063688E')
*/
/*****************************************************************************************************************************************************
*	Ngay tao:	20/07/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getparenstatus]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_getparenstatus]
GO
CREATE function dbo.f_getparenstatus(@sMenuId varchar(50))
Returns int
WITH ENCRYPTION
As
Begin
	Declare @index int
	select @index = count(PK_WEB_MENU) from T_WEB_MENU where FK_WEB_MENU = @sMenuId
Return @index
End
GO
/*
select dbo.f_getparenstatus('BFB00440-B363-487C-A186-3627B3140ABC')
*/
/*****************************************************************************************************************************************************
*	Ngay tao:	20/07/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Web_getOrderbyPosition]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Web_getOrderbyPosition]
GO
CREATE PROCEDURE [dbo].[Web_getOrderbyPosition]
	@p_position TinyInt		-- Vi tri hien thi/ ben trai/ ben phai/...
	,@p_level TinyInt
	,@p_menu_id Varchar(50)	
WITH ENCRYPTION
AS
	Declare @index int
	set @index = 1
	if @p_level = 0
		select @index = max(C_ORDER_LEVER1) from T_WEB_MENU where C_POSITION = @p_position
	if @p_level = 1
		select @index = max(C_ORDER_LEVER2) from T_WEB_MENU where C_LEVEL = 1 and FK_WEB_MENU = @p_menu_id
	if @p_level = 2
		select @index = max(C_ORDER_LEVER3) from T_WEB_MENU where C_LEVEL = 2 and FK_WEB_MENU = @p_menu_id	
	select @index as ORDERPOS
	Return 0
GO
/*
Exec Web_getOrderbyPosition '0','0',''
*/
/*****************************************************************************************************************************************************
*	Ngay tao:	08/06/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	Lay thong tin dua ra trang chu
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Web_HomeInfoGetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Web_HomeInfoGetAll]
GO
CREATE PROCEDURE [dbo].[Web_HomeInfoGetAll]
	@sWebMenuIdList nvarchar(4000)
	,@p_count_in_menu TinyInt
WITH ENCRYPTION
AS
	SET NOCOUNT ON
		Declare @Sql nvarchar(4000)
				,@icount Int				-- Bien luu so phan tu cua list	
				,@icountIndex Int			-- Bien luu so phan tu cua list	
				,@iIndex Int				-- Bien duyet phan tu tren list
				,@sIndexId Varchar(50)		-- Luu Id cua chi so 
		Create Table #T_ALL_ARTICLE(PK_WEB_ARTICLE uniqueidentifier,FK_WEB_MENU uniqueidentifier,C_TITLE nvarchar(1000),C_SHORT_CONTENT nvarchar(2000),C_IMAGE_NAME nvarchar(500),C_IMAGE_TITLE nvarchar(200),C_ORDER int)
		Create Table #T_ALL_MENU(PK_WEB_MENU uniqueidentifier)
		if @sWebMenuIdList is not null and @sWebMenuIdList <> ''
			Begin
				Set @iIndex = 0
				Set @sIndexId	= ''
				Set @icount = dbo.f_ListLen(@sWebMenuIdList,',')
				Set @iIndex = 1
					While @iIndex <= @icount
						Begin
							Set @sIndexId = dbo.f_ListGetAt(@sWebMenuIdList,@iIndex,',')
							select @icountIndex = count(PK_WEB_MENU) from T_WEB_MENU where FK_WEB_MENU = @sIndexId
							if @icountIndex > 0
							Begin
								Insert into #T_ALL_MENU (PK_WEB_MENU) select PK_WEB_MENU from T_WEB_MENU where FK_WEB_MENU = @sIndexId
								Insert into #T_ALL_MENU (PK_WEB_MENU) select PK_WEB_MENU from T_WEB_MENU where FK_WEB_MENU in (select PK_WEB_MENU from #T_ALL_MENU)
							End
							Set @Sql = ' Insert into #T_ALL_ARTICLE(PK_WEB_ARTICLE,FK_WEB_MENU,C_TITLE,C_SHORT_CONTENT,C_IMAGE_NAME,C_IMAGE_TITLE,C_ORDER) '
							Set @Sql = @Sql + 'Select TOP ' + Convert(varchar,@p_count_in_menu) + ' '
							Set @Sql = @Sql + 'Convert(varchar(50),PK_WEB_ARTICLE), ' + char(39) + @sIndexId + char(39) + ', C_TITLE, C_SHORT_CONTENT, dbo.f_GetAttachFileName (PK_WEB_ARTICLE,' + char(39) + 'WEB_IMAGE' + char(39) + ',' + char(39) + 'T_WEB_ARTICLE' + char(39) + ',' + char(39) + '!#~$|*' + char(39) + '), C_IMAGE_TITLE, C_ORDER' 
							Set @Sql = @Sql + ' From T_WEB_ARTICLE'
							if @icountIndex > 0
								Begin
									Set @Sql = @Sql + ' Where FK_WEB_MENU in (select PK_WEB_MENU from #T_ALL_MENU)'
								End 
							else
								Begin
									Set @Sql = @Sql + ' Where FK_WEB_MENU = '  + char(39) +  Convert(varchar(50),@sIndexId) + char(39)
								End
							Set @Sql = @Sql + ' and C_STATUS = 1'
							Set @Sql = @Sql + ' Order By C_ORDER DESC'
							print(@Sql)
							Exec (@Sql)	
							Delete 	#T_ALL_MENU
							Set @iIndex = @iIndex + 1				
						End
			End
		select 
		Convert(varchar(50),PK_WEB_ARTICLE) as PK_WEB_ARTICLE
		,Convert(varchar(50),FK_WEB_MENU) as FK_WEB_MENU
		,C_TITLE
		,C_SHORT_CONTENT
		,C_IMAGE_NAME
		,C_IMAGE_TITLE
		,C_ORDER
		from #T_ALL_ARTICLE
	SET NOCOUNT OFF
Return 0
GO
/*
Exec Web_HomeInfoGetAll '3F88D2AB-F96A-41D9-A4FC-FA9C7E4A91E1,23543C1A-08AB-43EF-B54F-C9A1A40055EF','5'
*/
/*****************************************************************************************************************************************************
*	Ngay tao:	08/06/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	Lay tin noi bat dua ra trang chu
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Web_HightlightGetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Web_HightlightGetAll]
GO
CREATE PROCEDURE [dbo].[Web_HightlightGetAll]
WITH ENCRYPTION
AS
	SET NOCOUNT ON
		select top 1
		Convert(varchar(50),PK_WEB_ARTICLE) as PK_WEB_ARTICLE
		,Convert(varchar(50),FK_WEB_MENU) as FK_WEB_MENU
		,C_TITLE
		,C_SHORT_CONTENT
		,dbo.f_GetAttachFileName (PK_WEB_ARTICLE,'WEB_IMAGE','T_WEB_ARTICLE','!#~$|*') as C_IMAGE_NAME
		,C_IMAGE_TITLE
		from T_WEB_ARTICLE
		where C_HIGHT_LIGHT =1
		Order by C_ORDER desc
	SET NOCOUNT OFF
Return 0
GO